﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
	public class CDC
	{
        public CDC()
		{
		}

		public List<CDCInfo> GetList(string value)
		{
            List<CDCInfo> list = new List<CDCInfo>();
			Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT PROSPECT_ID, PROSPECT_NO, KTP_NO, KTP_NAME, DATE_OF_BIRTH, IS_BI_CHECKING FROM TBL_PROSPECT WHERE (LTRIM(RTRIM(PROGRESS_STATUS)) LIKE 'CD') AND (KTP_NAME LIKE '%' + @NAME + '%' OR PROSPECT_NO LIKE '%' + @NAME + '%') ORDER BY PROSPECT_NO;");
            db.AddInParameter(cmd, "@NAME", DbType.String, value);
			using (IDataReader dr = db.ExecuteReader(cmd))
			{
				while (dr.Read())
				{
                    list.Add(new CDCInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetDateTime(4), dr.GetString(5)));
				}
                dr.Close();
			}

			return list;
		}

        public List<CDCInfo> GetListExportExcell(string value)
        {
            List<CDCInfo> list = new List<CDCInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT ROW_NUMBER() OVER(ORDER BY P.UPDATE_DATE DESC) AS [NO],
	                ISNULL(MS_SC.MS_STD_NAME, '') BUSINESS_MODEL, ISNULL(B.BRANCH_NAME, '') NAMA_UNIT, 
	                ISNULL(UM.EMAIL_ADDRESS, '') UM_EMAIL_ADDRESS, ISNULL(CM.EMAIL_ADDRESS, '') CM_EMAIL_ADDRESS, 
	                'Debitur Baru' TUJUAN_BI_CHECKING, P.PROSPECT_NO NO_APPLIKASI, P.NPWP_NO, 
	                P.FULL_NAME FIRST_NAME, P.FULL_NAME LAST_NAME, P.KTP_NO, 
	                P.FULL_NAME, P.DATE_OF_BIRTH TGL_LAHIR_FULL,
	                P.PLACE_OF_BIRTH, P.RENCANA_PLAFON,P.MALE_FEMALE, P.UPDATE_DATE
                FROM TBL_PROSPECT P 
                LEFT OUTER JOIN TBL_MS_BRANCH B ON P.UNIT_CODE = B.BRANCH_ID
                LEFT OUTER JOIN (SELECT MS_STD_ID, MS_STD_NAME FROM TBL_MS_STANDARD WHERE MS_STD_GRP_ID = 8) MS_SC ON MS_SC.MS_STD_ID = P.SOURCE_CODE
                LEFT OUTER JOIN 
                (
	                SELECT BRANCH_ID, EMAIL_ADDRESS 
	                FROM V_USER 
	                WHERE USER_TYPE = 'UM' GROUP BY BRANCH_ID, EMAIL_ADDRESS
                ) UM ON UM.BRANCH_ID = P.UNIT_CODE
                LEFT OUTER JOIN 
                (
	                SELECT V_USER.EMAIL_ADDRESS, CB.BRANCH_ID, CB.CLUSTER_ID  
	                FROM V_USER V_USER 
		                LEFT OUTER JOIN TBL_MS_CLUSTER_BRANCH CB ON V_USER.BRANCH_ID = CB.BRANCH_ID
	                WHERE V_USER.USER_TYPE = 'CM'
                ) CM ON CM.BRANCH_ID = P.UNIT_CODE
                WHERE (LTRIM(RTRIM(P.PROGRESS_STATUS)) LIKE 'CD') 
                    AND (P.KTP_NAME LIKE '%' + @NAME + '%' OR P.PROSPECT_NO LIKE '%' + @NAME + '%')
                ");
            db.AddInParameter(cmd, "@NAME", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new CDCInfo(dr.GetInt64(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), GetFirstName(dr.GetString(8)), GetLastName(dr.GetString(9)), dr.GetString(10), dr.GetString(11), dr.GetDateTime(12), dr.GetString(13), dr.GetDouble(14), dr.GetString(15), dr.GetDateTime(16)));
                }
                dr.Close();
            }

            return list;
        }

        private string GetFirstName(string FULL_NAME)
        {
            return FULL_NAME.Split(' ')[0];
        }

        private string GetLastName(string FULL_NAME)
        {
            return FULL_NAME.Split(' ')[FULL_NAME.Split(' ').Length - 1];
        }

        public void Save(string ProspectID, string IsBIChecking)
		{
			Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand(@"
                            	UPDATE TBL_PROSPECT SET IS_BI_CHECKING = @IS_BI_CHECKING
                                WHERE PROSPECT_ID LIKE '' + @PROSPECT_ID + '';");
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, ProspectID);
            db.AddInParameter(cmd, "@IS_BI_CHECKING", DbType.Int16, IsBIChecking);

            db.ExecuteNonQuery(cmd);
		}

        public List<string> GetParameter()
        {
            List<string> list = new List<string>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT PARAM
                FROM TBL_MS_CDC_PARAM");

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(dr.GetString(0));
                }
                dr.Close();
            }
            return list;
        }

        public List<string> GetParameterTable()
        {
            List<string> list = new List<string>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = 'TBL_PROSPECT' AND LEFT(COLUMN_NAME, 4) != 'RES_' AND COLUMN_NAME NOT IN (SELECT PARAM FROM TBL_MS_CDC_PARAM)");

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(dr.GetString(0));
                }
                dr.Close();
            }
            return list;
        }

        public void SaveParameter(List<string> param)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_MS_CDC_PARAM");
            db.ExecuteNonQuery(cmd);
            foreach (string item in param)
            {
                cmd = db.GetSqlStringCommand(@"INSERT INTO TBL_MS_CDC_PARAM VALUES (@PARAM)");
                db.AddInParameter(cmd, "@PARAM", DbType.String, item);
                db.ExecuteNonQuery(cmd);
            }
        }

        public DataSet GetListParameter(string value, string nip)
        {
            string field = "";
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT TOP 10 * FROM TBL_MS_CDC_PARAM");

            DataSet ds = db.ExecuteDataSet(cmd);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (i < ds.Tables[0].Rows.Count - 1) field += Convert.ToString(ds.Tables[0].Rows[i][0]).Trim() + ",";
                else field += Convert.ToString(ds.Tables[0].Rows[i][0]).Trim();
            }

            cmd = db.GetSqlStringCommand("SELECT " + field + ",IS_BI_CHECKING FROM TBL_PROSPECT WHERE (LTRIM(RTRIM(PROGRESS_STATUS)) LIKE 'CD') AND CDC_OPERATOR=@NIP AND (KTP_NAME LIKE '%' + @NAME + '%' OR PROSPECT_NO LIKE '%' + @NAME + '%') ORDER BY IS_CDC_URGENT DESC, UPDATE_DATE ASC, PROSPECT_NO;");
            db.AddInParameter(cmd, "@NAME", DbType.String, value);
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            ds = db.ExecuteDataSet(cmd);
            return ds;
        }

        public DataSet GetListParameterAll(string value)
        {
            string field = "";
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT TOP 10 * FROM TBL_MS_CDC_PARAM");

            DataSet ds = db.ExecuteDataSet(cmd);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (i < ds.Tables[0].Rows.Count - 1) field += Convert.ToString(ds.Tables[0].Rows[i][0]).Trim() + ",";
                else field += Convert.ToString(ds.Tables[0].Rows[i][0]).Trim();
            }

            cmd = db.GetSqlStringCommand("SELECT " + field + ",IS_BI_CHECKING FROM TBL_PROSPECT WHERE (LTRIM(RTRIM(PROGRESS_STATUS)) LIKE 'CD') AND CDC_OPERATOR='' AND (KTP_NAME LIKE '%' + @NAME + '%' OR PROSPECT_NO LIKE '%' + @NAME + '%') ORDER BY IS_CDC_URGENT DESC, UPDATE_DATE ASC, PROSPECT_NO;");
            db.AddInParameter(cmd, "@NAME", DbType.String, value);

            ds = db.ExecuteDataSet(cmd);
            return ds;
        }

        public DataSet GetListByID(string value)
        {
            string field = "";
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT TOP 10 * FROM TBL_MS_CDC_PARAM");

            DataSet ds1 = db.ExecuteDataSet(cmd);
            for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
            {
                if (i < ds1.Tables[0].Rows.Count - 1) field += Convert.ToString(ds1.Tables[0].Rows[i][0]).Trim() + ",";
                else field += Convert.ToString(ds1.Tables[0].Rows[i][0]).Trim();
            }

            cmd = db.GetSqlStringCommand("SELECT PROSPECT_ID," + field + " FROM TBL_PROSPECT WHERE PROSPECT_ID=@PROSPECT_ID;");
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, value);
            ds1 = db.ExecuteDataSet(cmd);
            return ds1;
        }

        public void SaveOperator(string nip, string prospectID)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_PROSPECT SET CDC_OPERATOR=@NIP, UPDATE_DATE=GETDATE() WHERE PROSPECT_ID=@PROSPECT_ID");
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospectID);
            
            db.ExecuteNonQuery(cmd);
        }

        public void setUrgent(string ProspectID)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_PROSPECT SET IS_CDC_URGENT = 1, UPDATE_DATE=GETDATE() WHERE PROSPECT_ID=@PROSPECT_ID");
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, ProspectID);            

            db.ExecuteNonQuery(cmd);
        }

        public void SetIsBIChecking(string prospectID)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_PROSPECT SET IS_BI_CHECKING='1', UPDATE_DATE=GETDATE() WHERE PROSPECT_ID=@PROSPECT_ID");
            
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospectID);

            db.ExecuteNonQuery(cmd);
        }

        public void SetReplacementOperator(string nip, string prospectID)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_PROSPECT SET CDC_OPERATOR=@NIP, UPDATE_DATE=GETDATE() WHERE PROSPECT_ID=@PROSPECT_ID");

            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospectID);

            db.ExecuteNonQuery(cmd);
        }

        /*
        public List<string> ListReplacementOp()
        {
            List<string> list = new List<string>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT DISTINCT NIP FROM TBL_PROSPECT WHERE CDC_OPERATOR <> '' AND IS_BI_CHECKING = '0'";
            cmd = db.GetSqlStringCommand(sql);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(dr.GetString(0));
                }
                dr.Close();
            }
            return list;
        }
         */

        public DataSet GetListReplacementOp(string value)
        {
            string field = "";
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT TOP 10 * FROM TBL_MS_CDC_PARAM");

            DataSet ds = db.ExecuteDataSet(cmd);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (i < ds.Tables[0].Rows.Count - 1) field += Convert.ToString(ds.Tables[0].Rows[i][0]).Trim() + ",";
                else field += Convert.ToString(ds.Tables[0].Rows[i][0]).Trim();
            }

            //cmd = db.GetSqlStringCommand("SELECT CDC_OPERATOR," + field + " FROM TBL_PROSPECT WHERE (LTRIM(RTRIM(PROGRESS_STATUS)) LIKE 'CD') AND CDC_OPERATOR <> '' AND IS_BI_CHECKING='0' AND (KTP_NAME LIKE '%' + @NAME + '%' OR PROSPECT_NO LIKE '%' + @NAME + '%') ORDER BY IS_CDC_URGENT DESC, UPDATE_DATE ASC, PROSPECT_NO;");
            cmd = db.GetSqlStringCommand("SELECT " + field + " FROM TBL_PROSPECT WHERE (LTRIM(RTRIM(PROGRESS_STATUS)) LIKE 'CD') AND CDC_OPERATOR <> '' AND IS_BI_CHECKING='0' AND (KTP_NAME LIKE '%' + @NAME + '%' OR PROSPECT_NO LIKE '%' + @NAME + '%') ORDER BY IS_CDC_URGENT DESC, UPDATE_DATE ASC, PROSPECT_NO;");
            db.AddInParameter(cmd, "@NAME", DbType.String, value);

            ds = db.ExecuteDataSet(cmd);
            return ds;
        }
	}
}